If Exists(Select xType, 1 From SysObjects Where Upper(Rtrim(Ltrim(Name))) = 'FX_LF_BUSCA_INF_COMPLEMENTAR')
	Drop Function FX_LF_BUSCA_INF_COMPLEMENTAR
Go

CREATE	FUNCTION FX_LF_BUSCA_INF_COMPLEMENTAR	(
													@ID_INTEGRACAO		SMALLINT,
													@CODIGOMATRIZFISCAL	VarChar(6),
													@OBJETO				VarChar(50),
													@DATAINICIAL		Datetime
												)
		RETURNS VARCHAR(100) AS
BEGIN												
	Declare	@Valor_Objeto VarChar(100)

	SELECT @VALOR_OBJETO = B.VALOR_OBJETO
	FROM 
		(SELECT 
			ID_INTEGRACAO_GOVERNO, COD_FILIAL, NOME_OBJETO, DATA_INICIAL = max(DATA_INICIAL) 
		 FROM 
			LF_INF_COMPLEMENTAR
		 WHERE 	ID_INTEGRACAO_GOVERNO	=	@ID_INTEGRACAO		AND 	
				COD_FILIAL				=	@CODIGOMATRIZFISCAL AND 
				NOME_OBJETO				=	@OBJETO				AND
		 		DATA_INICIAL			<=	@DATAINICIAL		AND
				(DATA_FINAL				IS	NULL				OR 
				 @DATAINICIAL			BETWEEN	DATA_INICIAL AND DATA_FINAL)
		 GROUP BY ID_INTEGRACAO_GOVERNO, COD_FILIAL, NOME_OBJETO) A
	JOIN LF_INF_COMPLEMENTAR B on 
			A.ID_INTEGRACAO_GOVERNO = B.ID_INTEGRACAO_GOVERNO	AND
			A.COD_FILIAL = B.COD_FILIAL							AND
			A.NOME_OBJETO = B.NOME_OBJETO						AND 
			A.DATA_INICIAL = B.DATA_INICIAL

	RETURN @Valor_Objeto
END
